﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ClientRegister
{
    public class EmployeesDataProvider
    {
        

        ConnectDB con = new ConnectDB();


        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataAdapter ada;

        public SqlDataReader Login(string Id, string Pass)
        {
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand("Select IDRole,FullName from Employees where ID = '" + Id + "' and Password = '" + Pass + "'", cnn);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public int EditPassword(string id, string old, string newp)
        {
            string query = string.Format("update Employees set Password = '{0}', NewRecord = 2 where ID = '{1}' and Password = '{2}'", newp, id, old);
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        public int changeInfo(string id, string name, string gender, string birth, string addr, string phone,
                              string role, string note)
        {
            Service.RegisterService gettime = new Service.RegisterService();
            string currtime = gettime.currentTime();
            string query = string.Format("update Employees set FullName = '{0}', IDgender = '{1}', Birthday = '{2}', Address = '{3}', "+
                "PhoneNum = {4}, IDRole = '{5}', Note = '{6}', NewRecord = 2, LastUpdate '{8}' where ID = '{7}'", name, gender, DateTime.Parse(birth), addr, phone, role, note, id, DateTime.Parse(currtime));
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        public int deleteEmp(string id)
        {
            string query = string.Format("Delete from Employees where ID = '{0}'",id);
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        public int AddEmployee(string ID, string pass, string name, string birthday, string idgender, string address, string phone, string idRole)
        {
            
            string query = string.Format("insert into Employees (ID,Password, FullName, Birthday, IDgender, Address,PhoneNum, IDRole, NewRecord, CreateDateTime) values " +
                "('{0}','{1}','{2}','{3}',{4},'{5}',{6},{7},{8},'{9}')", ID, pass, name, birthday, idgender, address, phone, idRole, 1, DateTime.Now.ToString());
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        public string SelectAndConvertXMLEmp(string idEmp)
        {
            string query = string.Format("select * from Employees where ID = '{0}' for xml auto,elements", idEmp);
            string xmlstring = "";
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);

            xmlstring = cmd.ExecuteScalar().ToString();
            return xmlstring;
        }
        //Lay id cua cot co record = 1
        public DataSet SelectIDNewRecord()
        {
            string query = string.Format("Select ID from Employees where NewRecord = 1");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecord()
        {
            string query = string.Format("update Employees set NewRecord = 0 where NewRecord = 1");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        }

        //Lay id cua cot co record = 2
        public DataSet SelectIDNewRecordUpdate()
        {
            string query = string.Format("Select ID from Employees where NewRecord = 2");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecordUpdate()
        {
            string query = string.Format("update Employees set NewRecord = 0 where NewRecord = 2");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        }

        public DataSet SelectAllEmp()
        {
            string query = string.Format("Select * from Employees");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
    }
}
